package com.hh.dao;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import java.sql.*;
import java.text.SimpleDateFormat;


public class PingGuDao {
    public final static String cfn = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

    public final static String url = "jdbc:sqlserver://212.129.155.194:1433;DatabaseName=jcyy_test";
    public final static String user = "sa";
    public final static String password = "ZJzhengg@163.com";
    private static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
    public JSONArray getPingGuList(String key, String pageSize, String mzhm) {
        Connection con = null;
        PreparedStatement statement = null;
        ResultSet res = null;
        JSONArray jsonArray = new JSONArray();
        try {
            Class.forName(cfn);
            con = DriverManager.getConnection(url, user, password);
            if(!pageSize.equals("")) {
                String sql = " select * from (select ROW_NUMBER() OVER(Order by pdfs) AS RowNumber,* from PD_RYPDB1 where TJBZ=1";

                if (mzhm != null && !mzhm.equals("")) {
                    sql += "and MZHM =" + mzhm;
                }
                if (key != null && !key.equals("")) {
                    sql += "and PD_RYPDB1.PDBMC LIKE '%" + key + "%' OR  PD_RYPDB1.PDRY LIKE '%" + key + "%'";
                }
                int page = Integer.parseInt(pageSize);
                sql += "  ) as b where  b.RowNumber BETWEEN (" + page + "-1)*6+1 and " + page + "*6 order by pdfs asc; ";
                System.out.println(sql);
                statement = con.prepareStatement(sql);
            }else {
                String sql = "select * from PD_RYPDB1 p where TJBZ=1 ";
                if(mzhm!=null && !mzhm.equals("")){
                    sql += "and MZHM = '"+mzhm+"'  Order by p.pdrq desc";

                }
                System.out.println(sql);
                statement = con.prepareStatement(sql);
            }

            res = statement.executeQuery();
            while (res.next()) {
                JSONObject o = new JSONObject();
                o.put("pdbmc", res.getString("pdbmc"));
                o.put("pdry", res.getString("pdry"));

                Date kdrq = res.getDate("kdrq");
                if(kdrq==null){
                    o.put("kdrq", "空");
                }else{
                    String time = simpleDateFormat.format(kdrq);
                    o.put("kdrq", time);
                }

                Date pdrq = res.getDate("pdrq");
                if(pdrq==null){
                    o.put("pdrq", "null");
                }else{
                    String time1 = simpleDateFormat.format(pdrq);
                    o.put("pdrq", time1);
                }
                o.put("tjbz", res.getInt("tjbz"));
                o.put("pdjg", res.getString("pdjg"));
                o.put("pdfs", res.getInt("pdfs"));
                jsonArray.add(o);
            }
            PingGuTotalDao pingGuTotalDao = new PingGuTotalDao();
            jsonArray.add(pingGuTotalDao.getPingGutToTal());
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        } finally {
            try {
                if (res != null) res.close();
                if (statement != null) statement.close();
                if (con != null) con.close();
            } catch (Exception e2) {
                // TODO: handle exception
                e2.printStackTrace();
            }
        }
        return jsonArray;
    }


}
